from DBSession import DBSession, Score, Stu, Course
from flask import Flask
from flask import request
from werkzeug.utils import secure_filename
from sqlalchemy import asc, desc

app = Flask(__name__)
session = DBSession()


@app.route("/scores", methods=['GET'])
def scores():
    # 构造查询
    query = session.query(Score, Stu.stu_name, Course.course_name).join(
        Stu, Stu.stu_no == Score.stu_no).join(
            Course, Course.course_id == Score.course_id)
    if (request.args.get('stu_no')):
        query = query.filter(Score.stu_no == request.args['stu_no'])
    if (request.args.get('stu_name')):
        query = query.filter(
            Stu.stu_name.like('%' + request.args['stu_name'] + '%'))
    # 每页条数
    page_size = 10
    if (request.args.get('page_size')):
        page_size = int(request.args['page_size'])
    # 页
    page_no = 1
    if (request.args.get('page_no')):
        page_no = int(request.args['page_no'])
    # 构造排序
    if (request.args.get('order_by') and request.args.get('order_dir')
            and request.args.get('order_dir') == 'desc'):
        query = query.order_by(desc(request.args['order_by']))
    elif request.args.get('order_by'):
        query = query.order_by(asc(request.args['order_by']))
    # 分页
    scores = query.limit(page_size).offset((page_no - 1) * page_size).all()
    # 带参数打印SQL
    print(query.statement.compile(compile_kwargs={"literal_binds": True}))
    list = []
    for item in scores:
        list.append({
            "stu_no": item.Score.stu_no,
            "stu_name": item.stu_name,
            "course_name": item.course_name,
            "score": item.Score.score
        })
    return list


if __name__ == "__main__":
    app.run(host='0.0.0.0', port=5000, debug=True)